﻿''' <summary>
''' clsUser类的数据库读取类
''' 对应表S_User
''' Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\自写系统\rjcwxt\Rjcwxt\data\sys.mdb;Persist Security Info=True
''' </summary>
Public Class ClsUserDbAccess
    Inherits ClsBaseDbAccess

    'Private mConnectionString As String '数据库连接字符串
    'Private mUserTableName As String 'S_User在库中的表名 S_User

    'Public Sub New(ByVal connString As String)
    '    mConnectionString = connString
    'End Sub

    'Public Sub New(ByVal connString As String, ByVal tableName As String)
    '    mConnectionString = connString
    '    mUserTableName = tableName

    'End Sub

    'Public Property ConnectionString As String
    '    Get
    '        Return mConnectionString
    '    End Get
    '    Set(value As String)
    '        mConnectionString = value
    '    End Set
    'End Property

    'Public Property UserTableName As String
    '    Get
    '        Return mUserTableName
    '    End Get
    '    Set(value As String)
    '        mUserTableName = value
    '    End Set
    'End Property

    Public Sub New(ByVal s As String)
        MyBase.New(s, TABLE_NAME_USER)
    End Sub

    Public Sub New(ByVal s As String, ByVal n As String)
        MyBase.New(s, n)
    End Sub

    ''' <summary>
    ''' 指定名称的用户
    ''' </summary>
    ''' <param name="name">登录名称</param>
    ''' <returns>集合列表</returns>
    Public Function GetUserByName(ByVal name As String) As List(Of ClsUser)
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection() 'New ClsDbAccessMdb(mConnectionString)
            Dim tmpParas(0) As OleDb.OleDbParameter
            Dim tmpList As List(Of ClsUser)
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select * from [" & TABLE_NAME_USER & "]  "
            tmpSql &= " where [UserName] = @UserName "

            tmpParas(0) = New OleDb.OleDbParameter("@UserName", OleDb.OleDbType.VarWChar)
            tmpParas(0).Value = name

            '查询并将返回数据解析为List对象
            tmpList = ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql))

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpList

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsUserDbAccess - DeleteUser"

            Return Nothing
        End Try
    End Function

    ''' <summary>
    ''' in条件查询userId
    ''' </summary>
    ''' <param name="idS"></param>
    ''' <returns></returns>
    Public Function GetUserByIdString(ByVal idS As String) As List(Of ClsUser)
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection()  'New ClsDbAccessMdb(mConnectionString)
            Dim tmpParas(0) As OleDb.OleDbParameter
            Dim tmpList As List(Of ClsUser)
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select * from [" & TABLE_NAME_USER & "]  "
            tmpSql &= " where [UserId] in ( " & idS & ") "

            '查询并将返回数据解析为List对象
            tmpList = ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql))

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpList

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsUserDbAccess - DeleteUser"

            Return Nothing
        End Try
    End Function

    Public Function DeleteUser(ByVal u As ClsUser) As Int32
        '删除指定用户
        '更新用户信息
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection()  'New ClsDbAccessMdb(mConnectionString)
            Dim tmpParas(0) As OleDb.OleDbParameter
            Dim tmpReturn As Int32
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "delete from [" & TABLE_NAME_USER & "]  "
            tmpSql &= " where [UserId]=@UserId "

            '构造参数集合

            tmpParas(0) = New OleDb.OleDbParameter("@UserId", OleDb.OleDbType.Integer)

            tmpParas(0).Value = u.UserId

            '查询并将返回数据解析为List对象
            tmpReturn = tmpDbAccess.UpdateRecord(tmpSql, tmpParas)

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpReturn

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsUserDbAccess - DeleteUser"

            Return Nothing
        End Try
    End Function
    Public Function UpdateUser(ByVal u As ClsUser) As Int32
        '更新用户信息
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection()  'New ClsDbAccessMdb(mConnectionString)
            Dim tmpParas(3) As OleDb.OleDbParameter
            Dim tmpReturn As Int32
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "update [" & TABLE_NAME_USER & "]  set "
            tmpSql &= "[UserName]=@UserName,[UserPassword]=@UserPassword,[UserIfAdmin]=@UserIfAdmin "
            tmpSql &= " where [UserId]=@UserId "

            '构造参数集合

            tmpParas(0) = New OleDb.OleDbParameter("@UserName", OleDb.OleDbType.VarWChar)
            tmpParas(1) = New OleDb.OleDbParameter("@UserPassword", OleDb.OleDbType.VarWChar)
            tmpParas(2) = New OleDb.OleDbParameter("@UserIfAdmin", OleDb.OleDbType.Boolean)
            tmpParas(3) = New OleDb.OleDbParameter("@UserId", OleDb.OleDbType.Integer)

            tmpParas(0).Value = u.UserName
            tmpParas(1).Value = u.UserPassword
            tmpParas(2).Value = u.UserIfAdmin
            tmpParas(3).Value = u.UserId

            '查询并将返回数据解析为List对象
            tmpReturn = (tmpDbAccess.UpdateRecord(tmpSql, tmpParas))

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpReturn

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsUserDbAccess - UpdateUser"

            Return Nothing
        End Try

    End Function

    Public Function InsertUser(ByVal u As ClsUser) As Int32
        '插入新用户
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection() 'New ClsDbAccessMdb(mConnectionString)
            Dim tmpInsertParas(2) As OleDb.OleDbParameter
            Dim tmpReturn As Int32
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "Insert into [" & TABLE_NAME_USER & "]"
            tmpSql &= " ([UserName],[UserPassword],[UserIfAdmin]) values("
            tmpSql &= "@UserName,@UserPassword,@UserIfAdmin"
            tmpSql &= ")"
            '构造参数集合

            tmpInsertParas(0) = New OleDb.OleDbParameter("@UserName", OleDb.OleDbType.VarWChar)
            tmpInsertParas(1) = New OleDb.OleDbParameter("@UserPassword", OleDb.OleDbType.VarWChar)
            tmpInsertParas(2) = New OleDb.OleDbParameter("@UserIfAdmin", OleDb.OleDbType.Boolean)

            tmpInsertParas(0).Value = u.UserName
            tmpInsertParas(1).Value = u.UserPassword
            tmpInsertParas(2).Value = u.UserIfAdmin

            '查询并将返回数据解析为List对象
            tmpReturn = (tmpDbAccess.InsertData(tmpSql, tmpInsertParas, True))

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpReturn

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsUserDbAccess - InsertUser"

            Return Nothing
        End Try
    End Function

    ''' <summary>
    ''' 返回S_User表内全部用户列表
    ''' </summary>
    ''' <returns>用户列表集合List(Of clsUser)</returns>
    Public Function GetAllUser() As List(Of ClsUser)
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection() 'New ClsDbAccessMdb(mConnectionString)
            Dim tmpList As List(Of ClsUser)
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select * from [S_User]"

            '查询并将返回数据解析为List对象
            tmpList = ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql))

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpList

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsUserDbAccess - GetAllUser"

            Return Nothing
        End Try
    End Function

    ''' <summary>
    ''' 返回指定编号的用户列表
    ''' </summary>
    ''' <param name="userId">用户编号</param>
    ''' <returns>用户列表集合List(Of clsUser)</returns>
    Public Function GetUserByUserId(ByVal userId As Integer) As List(Of ClsUser)
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection() 'New ClsDbAccessMdb(mConnectionString)
            Dim tmpList As List(Of ClsUser)
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select * from [" & TABLE_NAME_USER & "] where [UserId]=" & userId

            '查询并将返回数据解析为List对象
            tmpList = ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql))

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpList

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "clsUserDbAccess - GetAllUser"

            Return Nothing
        End Try
    End Function


    Public Overrides Function ConvertRecordToClass(ByVal tmpTableReader As OleDb.OleDbDataReader) As Object 'List(Of ClsUser)
        'UserId, UserName, UserPassword, UserRightFillVoucher, UserRightCheckEditVoucher,
        'UserRightPostVoucher, UserRightUnpostVoucher, UserRightCheckBank,
        'UserRightInitialize, UserRightSetupAccount, UserRightBackupData, UserRightRestoreData
        If tmpTableReader Is Nothing Then
            Return Nothing
        End If

        Try

            Dim tmpUserList As List(Of ClsUser) = New List(Of ClsUser)
            Dim tmpCls As ClsUser

            While tmpTableReader.Read()

                tmpCls = New ClsUser()

                With tmpCls

                    .UserId = tmpTableReader.GetInt32(0)
                    .UserName = tmpTableReader.GetString(1)
                    .UserPassword = tmpTableReader.GetString(2)
                    .UserIfAdmin = tmpTableReader.GetBoolean(3)

                End With

                tmpUserList.Add(tmpCls)

            End While

            Return tmpUserList

        Catch ex As Exception

            mErrorNumber = -1001
            mErrorMessage = ex.Message
            mErrorCreater = "clsUserDbAccess - ConvertRecordToUserClass"

            Return Nothing
        End Try

    End Function

End Class
